package com.smedi.ismedi.database.starter.interceptor.permission;

import com.baomidou.mybatisplus.annotation.TableName;
import com.smedi.ismedi.database.starter.annotation.SqlItem;
import com.smedi.ismedi.database.starter.annotation.SqlPermission;
import com.smedi.ismedi.database.starter.constant.DataBaseConstant;
import com.smedi.ismedi.database.starter.property.SqlPermissionProperty;
import com.smedi.ismedi.database.starter.util.ReflectUtil;
import lombok.RequiredArgsConstructor;
import lombok.extern.slf4j.Slf4j;
import net.sf.jsqlparser.JSQLParserException;
import net.sf.jsqlparser.expression.Expression;
import net.sf.jsqlparser.expression.operators.conditional.AndExpression;
import net.sf.jsqlparser.parser.CCJSqlParserUtil;
import net.sf.jsqlparser.schema.Table;
import net.sf.jsqlparser.statement.select.*;
import net.sf.jsqlparser.statement.update.Update;
import net.sf.jsqlparser.util.deparser.ExpressionDeParser;
import org.apache.ibatis.executor.statement.StatementHandler;
import org.apache.ibatis.mapping.BoundSql;
import org.apache.ibatis.mapping.MappedStatement;
import org.apache.ibatis.plugin.Interceptor;
import org.apache.ibatis.plugin.Intercepts;
import org.apache.ibatis.plugin.Invocation;
import org.apache.ibatis.plugin.Signature;
import org.apache.ibatis.reflection.DefaultReflectorFactory;
import org.apache.ibatis.reflection.MetaObject;
import org.apache.ibatis.reflection.SystemMetaObject;
import org.springframework.stereotype.Component;
import org.springframework.util.StringUtils;
import org.springframework.web.context.request.RequestContextHolder;
import org.springframework.web.context.request.ServletRequestAttributes;

import javax.servlet.http.HttpServletRequest;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.sql.Connection;
import java.util.*;
import java.util.stream.Collectors;

/**
 * @Author: xulinjun
 * @Date: 2021-7-28 13:29
 */
@Slf4j
@Intercepts({@Signature(type = StatementHandler.class, method = "prepare", args = {Connection.class, Integer.class})})
@Component
@RequiredArgsConstructor
public class SqlInterceptor implements Interceptor {
    private final SqlPermissionProperty sqlPermissionProperty;

    private final static String update = "UPDATE";

    private final static String select = "SELECT";

    /**
     * 获取权限数据
     *
     * @param key
     * @return
     * @throws ClassNotFoundException
     * @throws InvocationTargetException
     * @throws IllegalAccessException
     * @throws InstantiationException
     */
    private List<Long> getDataScope(String key) throws ClassNotFoundException, InvocationTargetException, IllegalAccessException, InstantiationException {
        ReflectUtil reflectUtil = new ReflectUtil();
        Map<String, List<Long>> maps = reflectUtil.invoke(sqlPermissionProperty.getAuthorityBean(), sqlPermissionProperty.getGetDataScope());
        if (maps.containsKey(key)) {
            return maps.get(key);
        } else {
            return null;
        }
    }

    /**
     * 获取当前人员是否为管理员
     *
     * @return
     * @throws ClassNotFoundException
     * @throws InvocationTargetException
     * @throws IllegalAccessException
     * @throws InstantiationException
     */
    private Boolean getIsAdmin() throws ClassNotFoundException, InvocationTargetException, IllegalAccessException, InstantiationException {
        ReflectUtil reflectUtil = new ReflectUtil();
        return reflectUtil.invoke(sqlPermissionProperty.getAuthorityBean(), sqlPermissionProperty.getIsAdminMethod());
    }

    /**
     * 获取人员id
     *
     * @return
     * @throws ClassNotFoundException
     * @throws InvocationTargetException
     * @throws IllegalAccessException
     * @throws InstantiationException
     */
    private Long getPersonId() throws ClassNotFoundException, InvocationTargetException, IllegalAccessException, InstantiationException {
        ReflectUtil reflectUtil = new ReflectUtil();
        return reflectUtil.invoke(sqlPermissionProperty.getAuthorityBean(), sqlPermissionProperty.getGetPersonIdMethod());
    }

    @Override
    public Object intercept(Invocation invocation) throws Throwable {

//        StatementHandler statementHandler = (StatementHandler) invocation.getTarget();
//        MetaObject metaObject = MetaObject.forObject(statementHandler, SystemMetaObject.DEFAULT_OBJECT_FACTORY, SystemMetaObject.DEFAULT_OBJECT_WRAPPER_FACTORY, new DefaultReflectorFactory());
//
//        // 初始化信息
//        MappedStatement mappedStatement = (MappedStatement) metaObject.getValue("delegate.mappedStatement");
//        String id = mappedStatement.getId();
//        String sqlCommand = mappedStatement.getSqlCommandType().toString();
//
//        // 获取到原始sql语句
//        BoundSql boundSql = statementHandler.getBoundSql();
//        String sql = boundSql.getSql();
//
//        if (sqlPermissionProperty.isEnableScope()) {
//            boolean continued = true;
//            if (update.equals(sqlCommand)) {
//                continued = sqlPermissionProperty.isUpdateHandler();
//            }
//
//            if (continued) {
//                boolean skipSqlPermission = false;
//
//                ServletRequestAttributes requestAttributes = (ServletRequestAttributes) RequestContextHolder.getRequestAttributes();
//                if(null==requestAttributes){
//                    return invocation.proceed();
//                }
//
//                Object value;
//                HttpServletRequest request = requestAttributes.getRequest();
//                if (request.getSession() != null) {
//                    value = request.getSession().getAttribute(DataBaseConstant.skipSqlPermission); // 同步情况
//                } else {
//                    value = RequestContextHolder.getRequestAttributes().getAttribute(DataBaseConstant.skipSqlPermission, 999); // 异步情况
//                }
//
//                if (value != null) {
//                    skipSqlPermission = Boolean.parseBoolean(value.toString());
//                }
//
//                log.debug("value: {}, skipSqlPermission: {}", value, skipSqlPermission);
//
//                // SQL注入
//                if (!skipSqlPermission) {
//                    String newSql = catchAnnotation(id, sql, sqlCommand);
//                    Field field = boundSql.getClass().getDeclaredField("sql");
//                    field.setAccessible(true);
//                    field.set(boundSql, newSql);
//
//                    log.debug("id: {}, sqlCommand: {}", id, sqlCommand);
//                    log.debug("enableScope: {}", sqlPermissionProperty.isEnableScope());
//                    log.debug("updateHandler: {}", sqlPermissionProperty.isUpdateHandler());
//                    log.debug("original sql: {}", sql);
//                    log.debug("rebuild update sql: {}", newSql);
//                }
//            }
//        }

        return invocation.proceed();
    }

    /**
     * 根据调用的接口获取注解，重构出新的SQL
     *
     * @param id
     * @param sql
     * @param sqlCommand
     * @return
     * @throws ClassNotFoundException
     * @throws JSQLParserException
     */
    public String catchAnnotation(String id, String sql, String sqlCommand) throws ClassNotFoundException, JSQLParserException, InvocationTargetException, NoSuchMethodException, IllegalAccessException, InstantiationException {
        Class<?> clazz = Class.forName(id.substring(0, id.lastIndexOf(".")));
        String methodName = id.substring(id.lastIndexOf(".") + 1);

        List<SqlItem> classAnnotations = new ArrayList<>();
        if (clazz.isAnnotationPresent(SqlPermission.class)) {
            classAnnotations = Arrays.asList(clazz.getAnnotation(SqlPermission.class).value());
        }

        // 判断当前方法上是否标注了注解
        List<SqlItem> methodAnnotations = new ArrayList<>();

        // 如果当前方法不包含，则调用的是标准接口
        List<Method> declaredMethods = new ArrayList<>(Arrays.asList(clazz.getDeclaredMethods()));
        Optional<Method> methodOptional = declaredMethods.stream().filter(method -> methodName.equals(method.getName()) || methodName.equals(method.getName() + "_mpCount")).findFirst();
        if (methodOptional.isPresent()) {
            Method method = methodOptional.get();
            // 获取class类上的注解，并且这个注解不包含在方法上。如果有相同类型，以方法上的注解为准
            if (method.isAnnotationPresent(SqlPermission.class)) {
                SqlPermission sqlPermission = method.getAnnotation(SqlPermission.class);
                List<SqlItem> list = new ArrayList(Arrays.asList(sqlPermission.value()));

                if (sqlPermission.includeClassAnnotations()) {
                    for (SqlItem classAnnotation : classAnnotations) {
                        Optional<SqlItem> sqlItemOptional = list.stream().filter(sqlItem -> sqlItem.item().equals(classAnnotation.item())).findFirst();
                        if (!sqlItemOptional.isPresent()) {
                            methodAnnotations.add(classAnnotation);
                        }
                    }
                }
                methodAnnotations.addAll(list);
            } else {
                methodAnnotations = classAnnotations;
            }
        } else {
            // 标准接口类，默认继承Class上的注解
            methodAnnotations = classAnnotations;
        }

        // 解析SQL，并根据注解进行SQL注入
        if (select.equals(sqlCommand)) {
            PlainSelect selectBody = (PlainSelect) ((Select) CCJSqlParserUtil.parse(sql)).getSelectBody();
            for (SqlItem sqlItem : methodAnnotations) {
                doSelect(selectBody, sqlItem, false, false);
            }
            return selectBody.toString();
        } else if (update.equals(sqlCommand)) {
            Update updateBody = (Update) CCJSqlParserUtil.parse(sql);
            for (SqlItem sqlItem : methodAnnotations) {
                doUpdate(updateBody, sqlItem);
            }
            return updateBody.toString();
        }

        return sql;
    }

    /**
     * 根据表名判断是否继续执行
     *
     * @param table
     * @param sqlItem
     * @param specifyTable
     * @return
     */
    private boolean validateTable(Table table, SqlItem sqlItem, boolean specifyTable) {
        if (specifyTable) {
            Class<?>[] entities = sqlItem.table();
            Optional<Class<?>> optional = Arrays.stream(entities).filter(entity -> entity.isAnnotationPresent(TableName.class))
                    .filter(entity -> table.getName().equals(entity.getAnnotation(TableName.class).value())).findFirst();

            return optional.isPresent();
        } else {
            return true;
        }
    }

    /**
     * 解决更新业务
     *
     * @param updateBody
     * @param sqlItem
     * @throws JSQLParserException
     * @throws InvocationTargetException
     * @throws NoSuchMethodException
     * @throws IllegalAccessException
     */
    private void doUpdate(Update updateBody, SqlItem sqlItem) throws JSQLParserException, InvocationTargetException, NoSuchMethodException, IllegalAccessException, ClassNotFoundException, InstantiationException {
        if (sqlItem.table().length <= 0) { // 判断当前注解是否有指定具体表
            if (updateBody.getJoins() != null) {
                log.error("放弃UPDATE注入，原因：类型[{}]，字段[{}]没有指定具体数据库表名！", sqlItem.item().getValue(), StringUtils.isEmpty(sqlItem.sqlField()) ? sqlItem.item().getField() : sqlItem.sqlField());
            } else {
                FromItem from = updateBody.getTable();
                if (from instanceof Table) {
                    addExpression(updateBody, from, sqlItem, false);
                }
            }
        } else {
            // from表处理
            FromItem from = updateBody.getTable();
            if (from instanceof Table) {
                addExpression(updateBody, from, sqlItem, true);
            }

            // 判断关联的表
            if (updateBody.getJoins() != null) {
                for (Join join : updateBody.getJoins()) {
                    FromItem fromItem = join.getRightItem();
                    if (fromItem instanceof Table) {
                        addExpression(updateBody, from, sqlItem, true);
                    }
                }
            }
        }

        // 判断是否存在where条件上的子查询
        doWhere(updateBody, sqlItem);
    }

    /**
     * 修改where条件
     *
     * @param t
     * @param sqlItem
     * @param <T>
     * @throws NoSuchMethodException
     * @throws InvocationTargetException
     * @throws IllegalAccessException
     * @throws JSQLParserException
     */
    private <T> void doWhere(T t, SqlItem sqlItem) throws NoSuchMethodException, InvocationTargetException, IllegalAccessException, JSQLParserException {
        // 获取方法
        Method setWhere = t.getClass().getMethod("setWhere", Expression.class);
        Method getWhere = t.getClass().getMethod("getWhere");

        // 判断是否存在where条件上的子查询
        Expression expression = null;
        if (getWhere.invoke(t) != null) {
            expression = (Expression) getWhere.invoke(t);
        }
        if (expression != null) {
            Expression expr = CCJSqlParserUtil.parseCondExpression(expression.toString());
            expr.accept(new ExpressionDeParser() {
                @Override
                public void visit(SubSelect subSelect) {
                    PlainSelect selectBody = (PlainSelect) subSelect.getSelectBody();
                    // 重构SQL
                    try {
                        // 影响查询时的子查询
                        if (sqlItem.influenceExpressionSubSelect()) {
                            doSelect(selectBody, sqlItem, false, true);
                        }
                    } catch (Exception e) {
                        e.printStackTrace();
                    }
                }
            });
            setWhere.invoke(t, expr);
        }
    }

    /**
     * 切割数组
     *
     * @param target
     * @param size
     * @param <T>
     * @return
     */
    private <T> List<List<T>> doCut(List<T> target, int size) {
        List<List<T>> listArr = new ArrayList<>();
        // 获取被拆分的数组个数
        int arrSize = target.size() % size == 0 ? target.size() / size : target
                .size() / size + 1;
        for (int i = 0; i < arrSize; i++) {
            List<T> sub = new ArrayList();
            // 把指定索引数据放入到list中
            for (int j = i * size; j <= size * (i + 1) - 1; j++) {
                if (j <= target.size() - 1) {
                    sub.add(target.get(j));
                }
            }
            listArr.add(sub);
        }
        return listArr;
    }

    /**
     * 添加条件
     *
     * @param t
     * @param from
     * @param sqlItem
     * @param specifyTable
     * @param <T>
     * @throws JSQLParserException
     * @throws NoSuchMethodException
     * @throws InvocationTargetException
     * @throws IllegalAccessException
     */
    private <T> void addExpression(T t, FromItem from, SqlItem sqlItem, boolean specifyTable) throws JSQLParserException, NoSuchMethodException, InvocationTargetException, IllegalAccessException, ClassNotFoundException, InstantiationException {
        Table table = (Table) from;
        String alias = Optional.ofNullable(table.getAlias()).map(als -> als.getName()).orElse("");

        if (validateTable(table, sqlItem, specifyTable)) {
            // 没有指定具体表的时候，可以在后端条件上直接拼接
            String field = (StringUtils.isEmpty(alias) ? "" : alias + ".") + (StringUtils.isEmpty(sqlItem.sqlField()) ? sqlItem.item().getField() : sqlItem.sqlField());

            Expression condition;
            if (SqlItemEnum.author.equals(sqlItem.item())) {
                condition = CCJSqlParserUtil.parseCondExpression(field + "=" + this.getPersonId());
            } else {
                List<Long> ids = this.getDataScope(sqlItem.item().getValue());
                if (ids != null) {
                    List<List<Long>> segmentIds = this.doCut(ids, 999);
                    String sql = "";
                    int i = 0;
                    for (List<Long> list : segmentIds) {
                        if (list.size() > 0) {
                            String str = list.stream().map(String::valueOf).collect(Collectors.joining(","));
                            sql += (i > 0 ? " or " : "") + "(" + field + " in (" + str + "))";
                            i++;
                        }
                    }
                    if (!StringUtils.isEmpty(sql)) {
                        sql = "(" + sql + ")";
                    }

                    if (StringUtils.isEmpty(sql)) {
                        condition = CCJSqlParserUtil.parseCondExpression("1 = 1");
                    } else {
                        condition = CCJSqlParserUtil.parseCondExpression(sql);
                    }
                } else {
                    condition = CCJSqlParserUtil.parseCondExpression("1 = 1");
                }
            }

            // 获取方法
            Method setWhere = t.getClass().getMethod("setWhere", Expression.class);
            Method getWhere = t.getClass().getMethod("getWhere");
            Expression where = null;
            if (getWhere.invoke(t) != null) {
                where = (Expression) getWhere.invoke(t);
            }

            if (where == null) {
                setWhere.invoke(t, condition);
            } else {
                AndExpression andExpression = new AndExpression(where, condition);
                setWhere.invoke(t, andExpression);
            }
        }
    }

    /**
     * 重构SQL语句
     *
     * @param selectBody
     * @param sqlItem
     * @throws JSQLParserException
     */
    private void doSelect(PlainSelect selectBody, SqlItem sqlItem, boolean fromSubSelectOriginal, boolean expressionSubSelectOriginal) throws JSQLParserException, InvocationTargetException, NoSuchMethodException, IllegalAccessException, ClassNotFoundException, InstantiationException {
        if (sqlItem.table().length <= 0) { // 判断当前注解是否有指定具体表
            // 如果有多个关联表，则不注入
            if (selectBody.getJoins() != null) {
                log.error("放弃SELECT注入，原因：类型[{}]，字段[{}]没有指定具体数据库表名！", sqlItem.item().getValue(), StringUtils.isEmpty(sqlItem.sqlField()) ? sqlItem.item().getField() : sqlItem.sqlField());
            } else {
                FromItem from = selectBody.getFromItem();
                if (from instanceof Table) {
                    // 如果是From条件中的子查询来源，则需要判断SqlItem上的标记
                    if (fromSubSelectOriginal) {
                        if (sqlItem.influenceFromSubSelect()) addExpression(selectBody, from, sqlItem, false);
                    } else {
                        addExpression(selectBody, from, sqlItem, false);
                    }
                } else if (from instanceof SubSelect) {
                    SubSelect subSelect = (SubSelect) from;
                    doSelect((PlainSelect) subSelect.getSelectBody(), sqlItem, true, false); // 子查询则嵌套
                }
            }
        } else {
            // from表处理
            FromItem from = selectBody.getFromItem();
            if (from instanceof Table) {
                addExpression(selectBody, from, sqlItem, true);
            } else if (from instanceof SubSelect) {
                SubSelect subSelect = (SubSelect) from;
                doSelect((PlainSelect) subSelect.getSelectBody(), sqlItem, true, false); // 子查询则嵌套
            }

            // 判断关联的表
            if (selectBody.getJoins() != null) {
                for (Join join : selectBody.getJoins()) {
                    FromItem fromItem = join.getRightItem();
                    if (fromItem instanceof Table) {
                        addExpression(selectBody, from, sqlItem, true);
                    } else if (fromItem instanceof SubSelect) {
                        SubSelect subSelect = (SubSelect) fromItem;
                        doSelect((PlainSelect) subSelect.getSelectBody(), sqlItem, true, false);
                    }
                }
            }
        }

        // 判断是否存在where条件上的子查询
        doWhere(selectBody, sqlItem);
    }
}
